package com.thunisoft.incremental.dao;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * @Author zhangxin-3
 * @Date 2020/3/26 12:12
 * @Version 1.0
 */
@Repository
public class TestDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

//    public int getCount(){
//        List<Map<String,Object>> result = new ArrayList<>();
//        Date start = new Date();
//        System.out.println(new Date());
//        jdbcTemplate.query("SELECT c_execid,c_caseno,c_court FROM test_etl.t_credit_execu_execu",new RowCallbackHandler(){
//            @Override
//            public void processRow(ResultSet row) throws SQLException {
//                do {
//                    System.out.println(row.getRow());
//                    Map<String,Object> map = new HashMap<>();
//                    map.put("c_execid",row.getString("c_execid"));
//                    map.put("c_caseno",row.getString("c_caseno"));
//                    map.put("c_court",row.getString("c_court"));
//                    result.add(map);
//                    if (result.size() > 1000) {
//                        insert(result);
//                        result.clear();
//                        System.out.println("导出 1000-"+new Date());
//                    }
//                }while (row.next());
//
//            }
//        });
//        Date end = new Date();
//        System.out.println(end.getTime() - start.getTime());
//        return (int)(end.getTime() - start.getTime());
//    }

    public long cus() throws IOException, SQLException {
        Date start = new Date();
        List<Map<String,Object>> result = new ArrayList<>();
        jdbcTemplate.query((conn -> {
            conn.setAutoCommit(false);
            PreparedStatement preparedStatement =
                    conn.prepareStatement("SELECT * FROM dw_view.v_archive_ent_base_info",
                            ResultSet.TYPE_FORWARD_ONLY,
                            ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setFetchSize(5000);
            preparedStatement.setFetchDirection(ResultSet.FETCH_FORWARD);
            return preparedStatement;
        }),(row -> {
            while (row.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("c_id",row.getString("c_id"));
                map.put("c_pripid",row.getString("c_pripid"));
                map.put("c_entname",row.getString("c_entname"));
                map.put("c_uniscid",row.getString("c_uniscid"));
                map.put("c_organcode",row.getString("c_organcode"));
                map.put("c_lerep",row.getString("c_lerep"));
                map.put("c_opstate_dimn",row.getString("c_opstate_dimn"));
                map.put("c_industryphy_dimn",row.getString("c_industryphy_dimn"));
                map.put("c_opscope",row.getString("c_opscope"));
                map.put("c_entstatus_dimn",row.getString("c_entstatus_dimn"));
                map.put("c_source",row.getString("c_source"));
                map.put("d_date",row.getString("d_date"));
                map.put("d_signdate",row.getString("d_signdate"));
                map.put("d_updatedate",row.getString("d_updatedate"));
                map.put("c_dom",row.getString("c_dom"));
                map.put("c_listcode",row.getString("c_listcode"));
                map.put("c_regno",row.getString("c_regno"));
                map.put("n_regcap",row.getString("n_regcap"));
                map.put("n_reccap",row.getString("n_reccap"));
                map.put("d_estdate",row.getString("d_estdate"));
                map.put("d_sandate",row.getString("d_sandate"));
                map.put("d_opto",row.getString("d_opto"));
                map.put("c_regorg_dimn",row.getString("c_regorg_dimn"));
                map.put("c_enttype_dimn",row.getString("c_enttype_dimn"));
                map.put("c_industry_dimn",row.getString("c_industry_dimn"));
                map.put("c_industry3_dimn",row.getString("c_industry3_dimn"));
                map.put("c_entsort_dimc",row.getString("c_entsort_dimc"));
                map.put("c_admtype_dimn",row.getString("c_admtype_dimn"));
                map.put("c_currency_dimn",row.getString("c_currency_dimn"));
                map.put("d_opfrom",row.getString("d_opfrom"));
                result.add(map);
                if (result.size() == 5000) {
                    insert(result);
                    result.clear();
                    System.out.println("导出" + row.getRow());
                }
            }
            return null;
        }));
        Date end = new Date();
        return (end.getTime() - start.getTime());
    }

    private void insert(List<Map<String,Object>> result){
        StringBuilder sb = new StringBuilder();
        sb.append("insert into test_etl.t_ent_base_info values ");
        for (Map<String, Object> stringObjectMap : result) {
            sb.append("(")
                    .append("'").append(stringObjectMap.get("c_id")).append("',")
                    .append("'").append(stringObjectMap.get("c_pripid")).append("',")
                    .append("'").append(stringObjectMap.get("c_entname")).append("',")
                    .append("'").append(stringObjectMap.get("c_uniscid")).append("',")
                    .append("'").append(stringObjectMap.get("c_organcode")).append("',")
                    .append("'").append(stringObjectMap.get("c_lerep")).append("',")
                    .append("'").append(stringObjectMap.get("c_opstate_dimn")).append("',")
                    .append("'").append(stringObjectMap.get("c_industryphy_dimn")).append("',")
                    .append("'").append(stringObjectMap.get("c_opscope")).append("',")
                    .append("'").append(stringObjectMap.get("c_entstatus_dimn")).append("',")
                    .append("'").append(stringObjectMap.get("c_source")).append("',")
                    .append("'").append(stringObjectMap.get("d_date")).append("',")
                    .append("'").append(stringObjectMap.get("d_signdate")).append("',")
                    .append("'").append(stringObjectMap.get("d_updatedate")).append("',")
                    .append("'").append(stringObjectMap.get("c_dom")).append("',")
                    .append("'").append(stringObjectMap.get("c_listcode")).append("',")
                    .append("'").append(stringObjectMap.get("c_regno")).append("',")
                    .append(stringObjectMap.get("n_regcap")).append(",")
                    .append(stringObjectMap.get("n_reccap")).append(",")
                    .append("'").append(stringObjectMap.get("d_estdate")).append("',")
                    .append("'").append(stringObjectMap.get("d_sandate")).append("',")
                    .append("'").append(stringObjectMap.get("d_opto")).append("',")
                    .append("'").append(stringObjectMap.get("c_regorg_dimn")).append("',")
                    .append("'").append(stringObjectMap.get("c_enttype_dimn")).append("',")
                    .append("'").append(stringObjectMap.get("c_industry_dimn")).append("',")
                    .append("'").append(stringObjectMap.get("c_industry3_dimn")).append("',")
                    .append("'").append(stringObjectMap.get("c_entsort_dimc")).append("',")
                    .append("'").append(stringObjectMap.get("c_admtype_dimn")).append("',")
                    .append("'").append(stringObjectMap.get("c_currency_dimn")).append("',")
                    .append("'").append(stringObjectMap.get("d_opfrom")).append("'")
                .append(")").append(",");

        }
        String sql = sb.substring(0,sb.length() - 1);
        jdbcTemplate.execute(sql);
    }

}
